package com.chancingpack.util;

import com.chancingpack.dal.model.Sgbasprodpicture;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import javax.servlet.http.HttpServletRequest;

/**
 * @ClassName: ImageLoadUtil
 * @Description: 图片加载工具类，为了将SQLServer中的二进制图片上传到项目路径下
 * @author: wu
 * @date: 2017年7月20日 上午8:54:29
 */
public class ImageLoadUtil {
    //判断图片是否已经下载
    private StringBuffer SQL =
            new StringBuffer("SELECT ORIG_IMAGE FROM [ERP_DB].[DBO].[SGBASPRODPICTURE]  WHERE ARCH_ID = ?");
    //查询所有的图片
    private static final StringBuffer SELECTSQL =
            new StringBuffer("SELECT ARCH_ID FROM [ERP_DB].[DBO].[SGBASPRODPICTURE] LEFT JOIN [ERP_DB].[DBO].[SGSLEPRODUCTEST] ON SGBASPRODPICTURE.arch_id=SGSLEPRODUCTEST.COST_ID WHERE CHECK_STATUS=0");

    private StringBuffer SQLCOUNT =
            new StringBuffer("SELECT count(*) as nums FROM [ERP_DB].[DBO].[SGBASPRODPICTURE] LEFT JOIN [ERP_DB].[DBO].[SGSLEPRODUCTEST] ON SGBASPRODPICTURE.arch_id=SGSLEPRODUCTEST.COST_ID WHERE CHECK_STATUS=0");

    private StringBuffer SQLINSERT =
            new StringBuffer("insert into [ERP_DB].[DBO].[wx_cache_pictures] (arch_id,states,cache_date) values (?,0,getdate())");

    private StringBuffer SQLUPDATE =
            new StringBuffer("update [ERP_DB].[DBO].[wx_cache_pictures] set states = 1 where arch_id = ?");

    private StringBuffer SQLQUERY =
            new StringBuffer("select arch_id as arEX from [ERP_DB].[DBO].[wx_cache_pictures] where states = 0");

    private StringBuffer SQLSTATUS =
            new StringBuffer("select count(picNum.num) as pics from" +
                    "    (select count(pic.arch_id) as num from [ERP_DB].[DBO].[wx_cache_pictures] pic where pic.states = 1 group by pic.arch_id) picNum");

    private StringBuffer SQLDEL = new StringBuffer("SELECT ARCH_ID as renum FROM [ERP_DB].[DBO].[SGBASPRODPICTURE] inner JOIN [ERP_DB].[DBO].[SGSLEPRODUCTEST] " +
            "ON SGBASPRODPICTURE.arch_id=SGSLEPRODUCTEST.COST_ID WHERE CHECK_STATUS!=0");


    /**
     * @param archIdList
     * @param request
     * @Title: readImgFromDb
     * @Description: 将SQLServer中的二进制图片上传到项目路径下
     * @return: void
     */
    public Map<String,Object> readImgFromDb(List<String> archIdList, HttpServletRequest request,String status) {
        boolean flag = false;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        OutputStream outs = null;
        InputStream ins = null;
        CompressUtil compressUtil = null;
        Map<String,Object> map = null;
        String msg = "缓存错误，请继续缓存";
        String arId = "成功";
        int insertsql = 0;
        int updatesql = 0;
        int querysql = 0;

        int count = 0;
        // 获取不带后缀的文件名称List集合


        //查询是否有过期的图片
        List<String> arIds = getRetiredPics(con,ps,rs);
        if(arIds.size() > 0){
            //若有，则删除相应的原图路径和缩略图路径下的过期图片
            boolean b = delFiles(getRetiredPics(con,ps,rs),request);
            System.out.println(b);
        }

        List<String> fileNameList1 = getFileList(request);
        List<String> fileNameList2 = getCoperssFileList(request);

        if(issuccuss(request)){
            msg = "已缓存完成！无需继续缓存";
            flag = true;
        }else if(!issuccuss(request)){
                if (archIdList != null) {
                    con = getCon();
                    //如果页面返回状态为缓存异常，则需要进行删除已经缓存部分的一个文件
                    if(status.equals("0")){
                        //页面出现缓存异常时选择继续缓存后，查询数据库中缓存状态失败的archId
                        ResultSet archIdEX = null;
                        String sqlquery = SQLQUERY.toString();
                        try{
                            ps = con.prepareStatement(sqlquery);
                            archIdEX = ps.executeQuery();
                            String archIdEXString = archIdEX.getString("arEX");
                            //获取存放原图目录下的以archId命名的一个文件，并删除
                            String realpath = getOriginalPicPath(request) + archIdEXString + ".jpg";
                            removeFile(realpath);
                        }
                        catch (Exception ex) {
                            ex.printStackTrace();
                        }
                    }
                    // 遍历所有的图形表中的存档编号arch_id
                    for (String archId : archIdList) {
                        // 判断存档编号arch_id是否是该文件夹中的某个文件的某个文件名称
                        if (!fileNameList1.contains(archId) || !fileNameList2.contains(archId)) {
                            // 如果没有相应以arch_id命名的文件，则进行文件上传操作
                            try {

                                // 上传相应原图到项目下WEB-INF/resource/upload/original文件夹中
                                outs = new FileOutputStream(getOriginalPicPath(request) + archId + ".jpg");
                                //获取archId，更新相应的文件的缓存状态为失败
                                String sqlinsert = SQLINSERT.toString();
                                ps = con.prepareStatement(sqlinsert);
                                ps.setString(1, archId.trim());
                                insertsql = ps.executeUpdate();
                                //更新缓存状态为失败后开始缓存
                                if (insertsql != 0) {
                                    // 查询相应存档编号arch_id的预编译SQL语句
                                    String sql = SQL.toString();
                                    ps = con.prepareStatement(sql);
                                    ps.setString(1, archId.trim());
                                    rs = ps.executeQuery();
                                    if (rs.next()) {
                                        int lengths = 0;
                                        ins = rs.getBinaryStream("orig_image");
                                        while ((lengths = ins.read()) != -1) {
                                            outs.write(lengths);
                                        }
                                        outs.flush();
                                        /**
                                         * 上传压缩图片到存放压缩图的文件夹中
                                         */
                                        compressUtil = new CompressUtil(getOriginalPicPath(request) + archId + ".jpg");
                                        compressUtil.resize(request, archId);
                                        System.out.println(archId + "uploading success!");
                                    }
                                }
                                //已经缓存文件，原图和缩略图，更新缓存状态为成功
                                String sqlupdate = SQLUPDATE.toString();
                                ps = con.prepareStatement(sqlupdate);
                                ps.setString(1, archId.trim());
                                updatesql = ps.executeUpdate();
                              //count += updatesql;
                            } catch (Exception ex) {
                                msg = "缓存出现异常";
                                ex.printStackTrace();
                            } finally {
                                //释放JDBC资源
//                                closeResource(con, ps, rs);
                                //关闭流
                                if (ins != null) {
                                    try {
                                        ins.close();
                                    } catch (Exception ex) {
                                        msg = "缓存出现异常";
                                        ex.printStackTrace();
                                    } finally {
                                        if (outs != null) {
                                            try {
                                                outs.close();
                                            } catch (Exception ex) {
                                                msg = "缓存出现异常";
                                                ex.printStackTrace();
                                            }
                                        }
                                    }
                                }
                            }
                        } else {
                            // 如果有相应以arch_id命名的文件，则不进行操作
                        }
                    }
                    //如果缓存完所有图片，设置flag和msg
                   try{
                        con = getCon();
                        //获取数据库中图片总数量
                       String sqlcount = SQLCOUNT.toString();
                       ps = con.prepareStatement(sqlcount);
                       ResultSet rs1 = ps.executeQuery();
                       //查询缓存状态数字之和，如果与数据库中所有图片数量相等，则缓存完成
                       if(rs1.next()){
                           count = rs1.getInt("nums");
                       }
                       int c = 0;
                       //获取数据库中状态缓存成功文件的数量
                       String sqlstatus = SQLSTATUS.toString();
                       ps = con.prepareStatement(sqlstatus);
                       ResultSet rs2 =  ps.executeQuery();
                       while(rs2.next()){
                           c = rs2.getInt("pics");
                           System.out.println(c+"-->"+count);
                       }
                       if(c == count){
                           flag = true;
                           msg = "全部文件已缓存成功";
                       }
                   }catch(Exception e){
                        e.printStackTrace();
                   } finally{
                       closeResource(con, ps, rs);
                   }
                }
        }
        map = new HashMap<String, Object>();
        map.put("msg",msg);
       // map.put("arId",arId);
        map.put("flag",flag);
        return map;
    }


    /**
     * 测试用
     * 查询原图存放路径下文件数量和缩略图路径下文件数量，如果都和数据库中相关文件数量相等，则返回true,缓存完成
     * @param request
     * @return
     */
    private boolean issuccues(HttpServletRequest request){
        boolean flag = false;
        int c = 0;
        File f1 = new File(getOriginalPicPath(request));
        File f2 = new File(getCompresslPicPath(request));
        //查询项目目录下存放原图的数量
        int n1 = countFile(f1);
        int n2 = countFile(f2);
        if((n1 == 3) && (n1 == n2)){
            flag = true;
        }
        return flag;
    }

    /**
     * 如果目标文件中图片数量和数据库中目标图片数量一致，则返回错误，无需继续查询
     * @param request
     * @return
     */
    private boolean issuccuss(HttpServletRequest request){
        boolean flag = false;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int c = 0;
        File f1 = new File(getOriginalPicPath(request));
        File f2 = new File(getCompresslPicPath(request));
        //查询项目目录下存放原图的数量
        int n1 = countFile(f1);
        int n2 = countFile(f2);
        //查询项目目录下存放原图的数量
        try{
            con = getCon();
            String sqlcount = SQLCOUNT.toString();
            ps = con.prepareStatement(sqlcount);
            rs =  ps.executeQuery();
            while(rs.next()){
                c= rs.getInt("nums");
            }
            //如果目标文件中图片数量和数据库中目标图片数量一致，则返回错误，无需继续查询
            if((c == n1) && (n1 == n2) ){
                return true;
            }
        }catch (Exception ex) {
            ex.printStackTrace();
        }finally{
            closeResource(con, ps, rs);
        }
        return false;
    }

    /**
     * 查询过期图片的ar_Id,
     * @param con
     * @param ps
     * @param rs1
     * @return
     */
    private List<String> getRetiredPics(Connection con, PreparedStatement ps,ResultSet rs1){
        List<String> ar_ids = new ArrayList<String>();
        try{
            con = getCon();
            //获取数据库中图片总数量
            String delsql = SQLDEL.toString();
            ps = con.prepareStatement(delsql);
            rs1 = ps.executeQuery();
            //查询缓存状态数字之和，如果与数据库中所有图片数量相等，则缓存完成
           while(rs1.next()){
                String ARCH_ID = rs1.getString("renum");
                ar_ids.add(ARCH_ID);
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        return ar_ids;
    }

    /**
     *
     * @return
     */
    public List<Sgbasprodpicture> getSgbasprodpictureObject() {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Sgbasprodpicture> sgbasprodpictureList = null;
        Sgbasprodpicture sgbasprodpicture = null;
        try {
            con = getCon();
            ps = con.prepareStatement(SELECTSQL.toString());
            rs = ps.executeQuery();
            sgbasprodpictureList = new ArrayList<Sgbasprodpicture>();
            int count = rs.getMetaData().getColumnCount();
            while (rs.next()) {//遍历行，next()方法返回值为Boolean，当存在数据行返回true,反之false
                sgbasprodpicture = new Sgbasprodpicture();
                loopCol:
                for (int i = 1; i <= count; i++) {//遍历列
                    if (i == 1) {
                        sgbasprodpicture.setArchId(rs.getString(i));
                        break loopCol;
                    }
                    /*System.out.print(rs.getString(i));
                    if(i < count) {
						System.out.print("---");//为数据隔开美观，没实际意义
					}*/
                }
                sgbasprodpictureList.add(sgbasprodpicture);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //释放JDBC资源
            closeResource(con, ps, rs);
        }
        return sgbasprodpictureList;
    }

    /**
     * @return
     * @Title: getCon
     * @Description: 获取JDBC连接
     * @return: Connection
     */
    public Connection getCon() {
        try {
            // 连接SQLServer2000
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
            Connection con = DriverManager.getConnection("jdbc:sqlserver://10.10.24.146:1433;databaseName=erp_db", "root",
                    "root");
            return con;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }

    /**
     * @param con
     * @param ps
     * @param rs
     * @Title: closeResource
     * @Description: 释放JDBC资源
     * @return: void
     */
    public static void closeResource(Connection con, PreparedStatement ps, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                if (ps != null) {
                    try {
                        ps.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    } finally {
                        if (con != null) {
                            try {
                                con.close();
                            } catch (Exception ex) {
                                ex.printStackTrace();
                            }
                        }
                    }
                }
            }
        }
    }

    /**
     * @param request
     * @return
     * @Title: getOriginalPicPath
     * @Description: 获取原图上传存放路径
     * @return: String
     */
    public static String getOriginalPicPath(HttpServletRequest request) {
        return request.getSession().getServletContext().getRealPath("/") + "WEB-INF/resource/upload/original/";
    }

    /**
     * @param request
     * @return
     * @Title: getCompresslPicPath
     * @Description: 获取压缩图上传存放路径
     * @return: String
     */
    public static String getCompresslPicPath(HttpServletRequest request) {
        return request.getSession().getServletContext().getRealPath("/") + "WEB-INF/resource/upload/compress/";
    }

    /**
     * @param request
     * @return
     * @Title: getFileList
     * @Description: 获取原图存放路径下面所有的文件名称List集合
     * @return: List<String>
     */
    public static List<String> getFileList(HttpServletRequest request) {
        File file = new File(getOriginalPicPath(request));
        // 获取文件夹路径下所有文件
        File[] files1 = file.listFiles();
        List<String> stringList = new ArrayList<String>();
        if (files1 != null) {
            for (File file2 : files1) {
                // 获取循环遍历出来的带.jpg后缀的文件名
                String fileNameWithJpg = file2.getName();
                // 获取不带带.jpg后缀的文件名
                String fileName = fileNameWithJpg.substring(0, fileNameWithJpg.length() - 4);
                stringList.add(fileName);
            }
        }

        return stringList;
    }

    /**
     * @param request
     * @return
     * @Title: getFileList
     * @Description: 获取缩略图存放路径下面所有的文件名称List集合
     * @return: List<String>
     */
    public static List<String> getCoperssFileList(HttpServletRequest request) {
        File file = new File(getCompresslPicPath(request));
        // 获取文件夹路径下所有文件
        File[] files = file.listFiles();
        List<String> stringList = new ArrayList<String>();
        if (files != null) {
            for (File file2 : files) {
                // 获取循环遍历出来的带.jpg后缀的文件名
                String fileNameWithJpg = file2.getName();
                // 获取不带带.jpg后缀的文件名
                String fileName = fileNameWithJpg.substring(0, fileNameWithJpg.length() - 4);
                stringList.add(fileName);
            }
        }

        return stringList;
    }

    /**
     * 查询某文件下的具体文件数量
     * @param file
     * @return
     */
    private int countFile(File file){
        String files[];
        files=file.list();
        int num = files.length;
        return num;
    }

    /**
     * 获取目录下所有文件(按时间排序)
     *
     * @param path
     * @return
     */
    public File getFileSort(String path) {

        List<File> list = getFiles(path, new ArrayList<File>());

        if (list != null && list.size() > 0) {

            Collections.sort(list, new Comparator<File>() {
                public int compare(File file, File newFile) {
                    if (file.lastModified() < newFile.lastModified()) {
                        return 1;
                    } else if (file.lastModified() == newFile.lastModified()) {
                        return 0;
                    } else {
                        return -1;
                    }
                }
            });
        }
        File f = list.get(list.size()-1);
        return f;
    }

    /**
     *
     * 获取目录下所有文件
     *
     * @param realpath
     * @param files
     * @return
     */
    public static List<File> getFiles(String realpath, List<File> files) {

        File realFile = new File(realpath);
        if (realFile.isDirectory()) {
            File[] subfiles = realFile.listFiles();
            for (File file : subfiles) {
                if (file.isDirectory()) {
                    getFiles(file.getAbsolutePath(), files);
                } else {
                    files.add(file);
                }
            }
        }
        return files;
    }

    /**
     * 如果图片过期了，查询已缓存完成的过期图片，并删除
     * @param ar_ids
     * @param request
     * @return
     */
    public boolean delFiles(List<String> ar_ids,HttpServletRequest request){
        int count1 = 0;
        int count2 = 0;
        List<String> flist2 = getCoperssFileList(request);
        List<String> flist1 = getFileList(request);
        for(String ar_id : ar_ids){
            for(String fileName : flist1){
                if(fileName.equals(ar_id)){
                    String realPth = getOriginalPicPath(request) + fileName + ".jpg";
                    boolean remove = removeFile(realPth);
                    if(remove){
                        count1 = count1 + 1;
                    }
                }
            }
        }
        for(String ar_id : ar_ids){
            for(String fileName : flist2){
                if(fileName.equals(ar_id)){
                    String realPth = getCompresslPicPath(request) + fileName + ".jpg";
                    boolean remove = removeFile(realPth);
                    if(remove){
                        count2 = count2 + 1;
                    }
                }
            }
        }
        if((count1 != 0) || (count2 != 0)){
            return true;
        }
        return false;
    }

    /**
     * 删除文件
     * @param realpath
     * @return
     */
    private boolean removeFile(String realpath) {
        boolean isRemove = false;
        File realFile = new File(realpath);
        if(realFile.exists() && realFile.isFile()){
            if(realFile.delete()){
                isRemove = true;
            }
        }
        return isRemove;
    }

}